package server_logic;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;

import javax.sql.rowset.serial.SerialBlob;

import server_gui.ServerLog;


import com.mysql.jdbc.PreparedStatement;

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;

import com.mysql.jdbc.Statement;

import entity.AddressingCustomer;
import entity.Area;
import entity.Boss;
import entity.CampaignPattern;
import entity.Classification;
import entity.Customer;

import entity.Employee;
import entity.MarketingCampaign;
import entity.Product;
import entity.Type;

public class Commands  {
	
	static int i=1;
	static int j=1;
	
	public Boss Search(Object obj) {
		Boss b = (Boss) obj;
		if(b.getControl().equals("EmployeeLoginController")){
			
			Employee emp = (Employee) b.getObj();
			Statement st;
			try {
				st = (Statement) CcrmServer.con.conn.createStatement();
				ResultSet rs = st.executeQuery("SELECT Role,Logged from ccrmg15.employee where Eid="+emp.getId()+" and Password="+emp.getPassword()+"");
					
					if(rs.next()){
						emp.setRole(rs.getString(1));
						emp.setLogged(rs.getString(2));
						ServerLog.log.addElement("Employee: "+emp.getId()+" is loged in");
					}
					else {
						b.setMsg("wrong id or pass");
			
						return b;
					}
					if(emp.getLogged().equals("no")){
						st.executeUpdate("UPDATE `ccrmg15`.`employee` SET `Logged`='yes' WHERE `Eid`="+emp.getId());
						emp.setLogged("yes");
						ServerLog.log.addElement(new Date().toString()+"User: "+emp.getId()+ " connected");
						b.setMsg("ok");
						return b;
					}
					
					b.setMsg("already logged");
					rs.close();
					st.close();
					}
			 catch (SQLException e) {
				e.printStackTrace();
			}
			return b;
	}
		System.out.println("returning null");
		return null;
}
	public Boss add(Object obj){
		Boss b=(Boss) obj;
		if(b.getControl().equals("CustomerServiceController")){
			Customer c=(Customer) b.getObj();
			Statement st;
			try {
				st = (Statement) CcrmServer.con.conn.createStatement();
				st.executeUpdate("INSERT INTO ccrmg15.customer VALUES('"+c.getCid()+"','"+c.getName()+"','"+c.getAge()+"','"+c.getArea()+"','"+c.getPhone()+"','"+1+"','"+1+"','2010-01-21');");
				ServerLog.log.addElement(new Date().toString()+"Customer : "+c.getCid()+ " added");
				b.setMsg("ok");
				st.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
				b.setMsg("failed");
			}	
	}
		return b;	
}
	public Boss logout(Object obj){
		Boss b=(Boss) obj;
		Employee emp = (Employee) b.getObj();
		Statement st;
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();
			st.executeUpdate("UPDATE `ccrmg15`.`employee` SET `Logged`='no' WHERE `Eid`="+emp.getId());
			ServerLog.log.addElement(new Date().toString()+"User: "+emp.getId()+ " disconnected");
			b.setMsg("logout");
		} catch (SQLException e) {
			b.setMsg("failed logout");
		}
		return b;
		
	}
	public Boss getCombo(Boss b) {
		ArrayList<String> areaNames=new ArrayList<String>();
				
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT aName from ccrmg15.area");
						
						while(rs.next())
						areaNames.add(rs.getString(1));
						b.setObj(areaNames);		
						rs.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}

	public Boss getArea(Boss b) {
		ArrayList<String> areaNames=new ArrayList<String>();
				
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT aName from ccrmg15.area");
						
						while(rs.next())
						areaNames.add(rs.getString(1));
						b.setObj(areaNames);
						rs.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	public Boss getProduct(Boss b) {
		ArrayList<String> productNames=new ArrayList<String>();
				
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT pName from ccrmg15.product");
						
						while(rs.next())
							productNames.add(rs.getString(1));
						b.setObj(productNames);	
						rs.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	
	public Boss newPattern(Object obj){
		Boss b=(Boss) obj;
		Statement st;
		CampaignPattern cp = (CampaignPattern) b.getObj();
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT cpID from ccrmg15.campaignpattern where cpID="+cp.getId()+"");		
				if(rs.next()){
					b.setMsg("pattern already exist");
				}
				else {
					st.executeUpdate("INSERT INTO ccrmg15.campaignpattern VALUES('"+cp.getId()+"','"+cp.getProduct()+"','"+cp.getArea()+"','"+cp.getFrom_age()+"','"+cp.getTo_age()+"','"+cp.getFromImportance()+"','"+cp.getToImportance()+"','"+cp.getFromInterest()+"','"+cp.getToInterest()+"','"+cp.getMessageTextBox()+"');");
					b.setMsg("pattern added OK");
				};
			}
		 catch (SQLException e) {
			e.printStackTrace();
		}
		return b;
	}
	public Boss getCustomer(Boss b) {
		Employee emp=(Employee) b.getObj();
		ArrayList<Integer> customer=new ArrayList<Integer>();
		Date d=new Date();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT DISTINCT Cid FROM ccrmg15.addressingcustomer WHERE addressingcustomer.Eid="+emp.getId()+"");
						while(rs.next())
							customer.add(rs.getInt(1));

						b.setObj(customer);	
						rs.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	
	public Boss getCampaign(Boss b) {
		AddressingCustomer ac=(AddressingCustomer) b.getObj();
		ArrayList<Integer> camp=new ArrayList<Integer>();
		Date d=new Date();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT CPid FROM ccrmg15.addressingcustomer WHERE Eid="+ac.geteId()+" AND Cid="+ac.getcId()+"");
						while(rs.next())
							camp.add(rs.getInt(1));
	
						b.setObj(camp);				
						rs.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	
	public Boss getSaledProduct(Boss b) {
		AddressingCustomer ac=(AddressingCustomer) b.getObj();
		ArrayList<Object> details=new ArrayList<Object>();
		Date d=new Date();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT cpProduct FROM ccrmg15.marketingcampaign AS m , campaignpattern AS c WHERE m.mcID ="+ac.getCpId()+" AND m.cpID = c.cpId");
						while(rs.next())
							details.add(rs.getObject(1));
						b.setObj(details);				
						rs.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	
	public Boss getFuturePermission(Boss b) {
		AddressingCustomer ac=(AddressingCustomer) b.getObj();
		ArrayList<String> categories=new ArrayList<String>();
		Date d=new Date();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT catName FROM ccrmg15.category AS c WHERE NOT EXISTs (SELECT p.category FROM permissionmarketing AS p WHERE p.cID="+ac.getcId()+" AND c.catName=p.category)");
						while(rs.next())
							categories.add(rs.getString(1));
						b.setObj(categories);				
						rs.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	public Boss NewSaleOrDeny(Boss b) {
		AddressingCustomer ac=(AddressingCustomer) b.getObj();
		ArrayList<String> categories=ac.getCategories_permit();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					st.executeUpdate("DELETE FROM `ccrmg15`.`addressingcustomer` WHERE `Eid`="+ac.geteId()+" and`Cid`= "+ac.getcId()+" and`CPid`="+ac.getCpId()+"");
						for(int i=0;i<categories.size();i++){
							st.executeUpdate("INSERT INTO `ccrmg15`.`permissionmarketing` (`cID`, `category`) VALUES ('"+ac.getcId()+"', '"+categories.get(i)+"')");
						}
						if(ac.getSaleFlag())
						st.executeUpdate("INSERT INTO `ccrmg15`.`clienthistory` (`cID`, `cpID`, `eID`, `saleFlag`, `freeText`, `item`, `quantity` , `phonetime`) VALUES ('"+ac.getcId()+"', '"+ac.getCpId()+"', '"+ac.geteId()+"', '1', '"+ac.getFreeText()+"', '"+ac.getItem()+"', '"+ac.getQuntity()+"' , '"+ac.getPhoneCall()+"')");
						else
							st.executeUpdate("INSERT INTO `ccrmg15`.`clienthistory` (`cID`, `cpID`, `eID`, `saleFlag`, `freeText`, `item`, `quantity`, `phonetime`) VALUES ('"+ac.getcId()+"', '"+ac.getCpId()+"', '"+ac.geteId()+"', '0', '"+ac.getFreeText()+"', '"+ac.getItem()+"', '"+ac.getQuntity()+"' , '"+ac.getPhoneCall()+"' )");
						st.close();
						b.setObj(categories);
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	public Boss getProductNames(Boss b) {
		
		ArrayList<String> productNames=new ArrayList<String>();
		Statement st;
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();

			ResultSet rs = st.executeQuery("SELECT pName from ccrmg15.product");
				
				while(rs.next())
					productNames.add(rs.getString(1));
				b.setObj(productNames);
		
				rs.close();
				st.close();
				return b;
				
		} catch (SQLException e) {
		
			e.printStackTrace();
		}

		return null;
	}
	public Boss deleteProduct(Boss b) {
		Product p=(Product) b.getObj();
		Statement st;
		try {
			//System.out.println("name = "+p.getName());
			String name = p.getName();
			st = (Statement) CcrmServer.con.conn.createStatement();
			st.executeUpdate("DELETE FROM `ccrmg15`.`product` WHERE `pName`='"+name+"'");
			//st.executeUpdate("DELETE FROM 'ccrmg15'.'product' WHERE 'pName'= "+p.getName());
				st.close();
				b.setMsg("product deleted successefully");
				return b;
				
		} catch (SQLException e) {
		
			e.printStackTrace();
		}

		return null;
	}
	public Boss addProduct(Boss b) {
		Product p=(Product) b.getObj();
		Statement st;
		try {

			
			Blob blob = new SerialBlob(p.getMybytearray());
			PreparedStatement ps = (PreparedStatement) CcrmServer.con.conn.prepareStatement("INSERT INTO `ccrmg15`.`product` (`pName`, `description`, `price`,`photo`) VALUES (?,?,?,?)");
			ps.setString(1,p.getName());
			ps.setBlob(4,blob);
			ps.setString(2, p.getDescription());
			ps.setFloat(3,p.getPrice());
			ps.executeUpdate();
			ps.close();
				b.setMsg("product added successefully");
				return b;
				
		} catch (SQLException e) {
		
			e.printStackTrace();
			b.setMsg("could not add this product .");
			return b; 
			//change return msg - for showing if the item added or not 
		}
		
	}
	
	public Boss analyze(Boss b){
				Statement st;
				ResultSet r;
				try {
						st = (Statement) CcrmServer.con.conn.createStatement();
						st.executeUpdate("Drop view if exists t1");
						st.executeUpdate("create view t1 AS(SELECT c.CId as cID ,SUM(ch.quantity)as total_q,sum(ch.quantity*p.price)as total_s from clienthistory AS ch,customer as c,product as p where ch.saleFlag='1'and c.CId=ch.cID and ch.item=p.pName GROUP BY c.CId)");
						r = st.executeQuery("select * from t1");
												
						while(r.next()){
							System.out.println(r.getString(3));
							int sum=(int)(r.getDouble(3));
							sum/=1000;
							String cid=r.getString(1);
							if(sum>0 && sum<=10 ){
								st.executeUpdate("UPDATE `ccrmg15`.`customer` SET `CImportance`="+sum+" WHERE `CId`="+cid+";");
							}
						}
						
						
						b.setObj("ok");				
						r.close();
						st.close();
						return b;
						
				} catch (SQLException e) {
					e.printStackTrace();
				}
		return null;
	}
	
	public Boss getCategoryCombo(Boss b){
		ArrayList<String> categories=new ArrayList<String>();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT catName FROM ccrmg15.category");
						while(rs.next())
							categories.add(rs.getString(1));
						b.setObj(categories);				
						rs.close();
						st.close();
						return b;		
						
					} catch (SQLException e) {
							e.printStackTrace();
							}
					return null;
			}

	public Boss gettypeCombo(Boss b){
		ArrayList<String> types=new ArrayList<String>();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT typeName FROM ccrmg15.type");
						while(rs.next())
							types.add(rs.getString(1));
						b.setObj(types);				
						rs.close();
						st.close();
						return b;		
						
					} catch (SQLException e) {
							e.printStackTrace();
							}
					return null;
			}
	
	
	public Boss addarea(Boss b){
		Area a=(Area) b.getObj();
		Statement st;
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT aID from ccrmg15.area where aID="+a.getAid()+"");
				while(rs.next()){
					b.setMsg("area already exist");		
					rs.close();
					st.close();
					return b;	
				}
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					st.executeUpdate("INSERT INTO `ccrmg15`.`area` (`aId`, `aName`) VALUES ('"+a.getAid()+"', '"+a.getAreaName()+"');");
					b.setMsg("areaadded");
					return b;
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				
			} catch (SQLException e) {
			}
				
			
			return null;
	}


	public Boss getTypes(Boss b) {
				ArrayList<String> types=new ArrayList<String>();
		Statement st;
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT typeName FROM ccrmg15.type");
				while(rs.next())
					types.add(rs.getString(1));
				b.setObj(types);				
				rs.close();
				st.close();
				return b;		
				
			} catch (SQLException e) {
					e.printStackTrace();
					}
		return null;
	}
	public Boss deleteType(Boss b) {
		Type t=(Type) b.getObj();
		Statement st;
		try {
			//System.out.println("name = "+p.getName());
			String name = t.getTypeName();
			st = (Statement) CcrmServer.con.conn.createStatement();
			st.executeUpdate("DELETE FROM `ccrmg15`.`type` WHERE `typeName`='"+name+"'");
			
				st.close();
				b.setMsg("type deleted successefully");
				return b;
				
		} catch (SQLException e) {
		
			e.printStackTrace();
		}
		return null;
	}
	public Boss addType(Boss b) {
		// TODO Auto-generated method stub
		//INSERT INTO `ccrmg15`.`type` (`typeName`) VALUES ('klasser');
		Type t=(Type) b.getObj();
		Statement st;
		try {
			//System.out.println("name = "+p.getName());
			String name = t.getTypeName();
			//System.out.println(name);
			st = (Statement) CcrmServer.con.conn.createStatement();
			st.executeUpdate("INSERT INTO `ccrmg15`.`type` (`typeName`) VALUES ('"+name+"')");
			
				st.close();
				b.setMsg("type added successefully");
				return b;
				
		} catch (SQLException e) {
			b.setMsg("could not add this type ");
			return b;
		}
	}


	public Boss addCategoryToTable(Boss b) {
		//Statement st;
	//	st.executeUpdate("INSERT INTO `ccrmg15`.`clienthistory` (`cID`) VALUES ('"+ac.getcId()+"')");
		Statement st;
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();
			st.executeUpdate("INSERT INTO `ccrmg15`.`category` (`catName`) VALUES ('"+b.getObj()+"')");
				st.close();
				b.setMsg("Category successfully addad");
				return b;		
				
			} catch (SQLException e) {
				b.setMsg("Category already exist");
				return b;
					}
	}


		
	
	
	public Boss deletearea(Boss b){
		Area a=(Area) b.getObj();
		Statement st;
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();
			st.executeUpdate("DELETE FROM `ccrmg15`.`area` WHERE `aName`='"+a.getAreaName()+"';");
			b.setMsg("deletedsuccessfully");			
			} catch (SQLException e) {
			
			}
			return b;
		
	}
	public Boss getproductsCombo(Boss b){
		ArrayList<String> products=new ArrayList<String>();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					ResultSet rs = st.executeQuery("SELECT pName FROM ccrmg15.product");
						while(rs.next())
							products.add(rs.getString(1));
						b.setObj(products);				
						rs.close();
						st.close();
						return b;		
						
					} catch (SQLException e) {
							e.printStackTrace();
							}
					return null;
			}
	public Boss classify(Boss b){
		Classification c=(Classification) b.getObj();
				Statement st;
				try {
					st = (Statement) CcrmServer.con.conn.createStatement();
					//ResultSet rs = st.executeQuery("SELECT catName from ccrmg15.classification where catName="+c.getCategory()+" and typeName="+c.getType()+" and pName="+c.getProduct()+"");
						//if(rs.next())
							
						//else {
							 try{
								 st.executeUpdate("INSERT INTO `ccrmg15`.`catalogue` (`catName`, `typeName`, `pName`) VALUES ('"+c.getCategory()+"', '"+c.getType()+"', '"+c.getProduct()+"')");
							 }catch (SQLException e){
								 b.setMsg("Classification already exist");
								 return b;
							 }
						//}
						b.setObj(c);				
						//rs.close();
						st.close();
						b.setMsg("Classification successfull");
						return b;		
						
					} catch (SQLException e) {
							e.printStackTrace();
							}
					return null;
			}


	public Boss getCustomerReports(Boss b){
		
		Statement st;
		String str="";
		ArrayList<String> customer = new ArrayList<String>();
		int colCount = 0;
		ResultSet r;
		java.util.Date date = new Date();
		
			try {
				st = (Statement) CcrmServer.con.conn.createStatement();
				st.executeUpdate("Drop  view if exists v"+i+"" );
				st.executeUpdate("create view v"+i+" as(select c.CId as CId, c.CName as CName,(c.CImportance + c.CLevelOfItnterest) as rating FROM customer as c order by rating)");
				String q = "SELECT * FROM v"+i+"";	
				i++;
				r = st.executeQuery(q);

				while(r.next()){
				str+=r.getString(1)+" ";
				str+=r.getString(2)+" ";
				str+=r.getString(3);
				customer.add(str);
				str="";
				}
				r.close();
				st.close();	
			
				
				} catch (SQLException e) {
						e.printStackTrace();
						}
			
			
			b.setObj(customer);
			return b;
		}

public Boss getCampainPatterns(Boss b) {
ArrayList<String> patterns=new ArrayList<String>();
Statement st;
try {
	st = (Statement) CcrmServer.con.conn.createStatement();
	ResultSet rs = st.executeQuery("SELECT cpId FROM ccrmg15.campaignpattern");
		while(rs.next()){
			patterns.add(rs.getString(1));
			//System.out.println(rs.getString(1));
		}
		b.setObj(patterns);
		
		rs.close();
		st.close();
		return b;		
		
	} catch (SQLException e) {
			e.printStackTrace();
			}
return null;
}
public Boss initiatePatterns(Boss b) {
	// TODO Auto-generated method stub
	ArrayList<String> customers=new ArrayList<String>();
	MarketingCampaign mc =(MarketingCampaign) b.getObj();
	Statement st;
	try {
		st = (Statement) CcrmServer.con.conn.createStatement();
			//@SuppressWarnings("deprecation")
			@SuppressWarnings("deprecation")
			java.sql.Date startDate = new java.sql.Date(mc.getStartDate().getYear(),mc.getStartDate().getMonth(),mc.getStartDate().getDay()+1);
			java.sql.Date endDate = new java.sql.Date(mc.getEndDate().getYear(),mc.getEndDate().getMonth(),mc.getEndDate().getDay()+1);

			try{
					// st.executeUpdate("INSERT INTO `ccrmg15`.`classification` (`catName`, `typeName`, `pName`) VALUES ('"+c.getCategory()+"', '"+c.getType()+"', '"+c.getProduct()+"')");
				PreparedStatement ps = (PreparedStatement) CcrmServer.con.conn.prepareStatement("INSERT INTO `ccrmg15`.`marketingcampaign` (`mcName`, `startDate`, `endDate`, `cpID`, `mcID`) VALUES (?,?,?,?,?)");
				ps.setInt(5,mc.getMcId());
				ps.setString(1, mc.getName());
				ps.setInt(4,mc.getPatternID());
				ps.setString(2,startDate.toString());
				ps.setString(3,endDate.toString());
				ps.executeUpdate();
				ps.close();
				ResultSet rs = st.executeQuery("Select distinct pm.cID from permissionmarketing as pm, customer as c, campaignpattern as cp, catalogue as ct where pm.cID = c.CId and c.age >= cp.fromAge and c.age <= cp.toAge and c.CImportance >= cp.fromImportance and c.CImportance <= cp.toImportance and c.Area = cp.area and c.CLevelOfItnterest >= cp.fromInterest and c.CLevelOfItnterest <= toInterest and pm.category = ct.catName and ct.pName = cp.cpProduct and cp.cpId = '"+mc.getPatternID()+"'");
				//get customers with permission level granted . //2015-03-04
				int counter = 0;
				while(rs.next()){
					customers.add(rs.getString(1));
					counter++;
				}
				rs = st.executeQuery("Select Eid from ccrmg15.employee where Role='Salesman'");
				while(rs.next()){
					customers.add(rs.getString(1));
				}
				customers.add(String.valueOf(counter));
				b.setObj(customers);
				
				 }catch (SQLException e){
					 e.printStackTrace();
					 b.setMsg("allready in db");
					 return b;
				 }
			

							
			
			st.close();
			b.setMsg("Campaign initialized");
			return b;		
			
		} catch (SQLException e) {
				e.printStackTrace();
				}

	
	return null;
}

public Boss Combine(Boss b) {
	AddressingCustomer ac=(AddressingCustomer) b.getObj();

	try {
		PreparedStatement ps = (PreparedStatement) CcrmServer.con.conn.prepareStatement("INSERT INTO `ccrmg15`.`addressingcustomer` (`Eid`, `Cid`, `CPid`) VALUES (?,?,?)");
		ps.setInt(1,ac.geteId() );
		ps.setInt(2,ac.getcId());
		ps.setInt(3, ac.getCpId());
		ps.executeUpdate();
		b.setMsg("added");
		return b;
	}catch(SQLException e){
		b.setMsg("cant add");
		return b ;
	}
	//

}
public Boss getCampaignReports(Boss b) {
	
	Statement st;
	String str="";
	ArrayList<String> campaign = new ArrayList<String>();
	int colCount = 0;
	ResultSet r;
		try {
			st = (Statement) CcrmServer.con.conn.createStatement();
			int id = Integer.parseInt(b.getMsg());
			st.executeUpdate("drop view if exists n1");
			st.executeUpdate("drop view if exists n2");
			st.executeUpdate("create view n1 as (select  `h`.`cpID` AS `mcID`, count(`h`.`cID`) AS `customers`, sum(`h`.`phonetime`) AS `phonetime` from `clienthistory` `h` where (`h`.`cpID` = '"+id+"'))");
			st.executeUpdate("create view n2 as (select  `h`.`cpID` AS `mcID`, count(`h`.`saleFlag`) AS `saleFlag` from `clienthistory` `h` where ((`h`.`cpID` = '"+id+"') and (`h`.`saleFlag` = '1')))");
			
			//r? saves the free text 
			//p? saves all other fields 
			st.executeUpdate("drop view if exists p"+j);
			st.executeUpdate("drop view if exists r"+j);
			st.executeUpdate("create view p"+j+" as (select  `n1`.`mcID` AS `mcID`, `n1`.`customers` AS `customers`, `n1`.`phonetime` AS `phonetime`, `n2`.`saleFlag` AS `saleFlag` from (`n1` join `n2`))");
			st.executeUpdate("create view r"+j+" as (select  `h`.`freeText` AS `freeText` from `clienthistory` `h` where ((`h`.`cpID` = '"+id+"') and (`h`.`saleFlag` = '0')))");
			
			
			String q = "SELECT * FROM p"+j+"";	
			String k = "SELECT * FROM r"+j+"";
			j++;
			
			r = st.executeQuery(q);

			while(r.next()){
			str+="Marketing Campaign Id:"+r.getString(1)+" ";
			str+="Number of potential customers :"+r.getString(2)+" ";
			str+="Total phone time :"+r.getString(3)+" ";
			str+="Sell events :"+r.getString(4)+" ";
			}
			r=st.executeQuery(k);
			str +="REPORTS: ";
			while(r.next()){
				str+=r.getString(1)+" ";
			}
			campaign.add(str);
			str="";
			r.close();
			st.close();	
		
			
			} catch (SQLException e) {
					e.printStackTrace();
					}
		
		
		b.setObj(campaign);
		return b;
	
}
public Boss getCampaigns(Boss b) {
	ArrayList<String> campaigns=new ArrayList<String>();
	Statement st;
	try {
		st = (Statement) CcrmServer.con.conn.createStatement();
		ResultSet rs = st.executeQuery("SELECT mcID FROM ccrmg15.marketingcampaign");
			while(rs.next()){
				campaigns.add(rs.getString(1));
				//System.out.println(rs.getString(1));
			}
			b.setObj(campaigns);
			
			rs.close();
			st.close();
			return b;		
			
		} catch (SQLException e) {
				e.printStackTrace();
				}
	return null;
}

public Boss addpermission(Boss b){
	ArrayList<String> campaigns=new ArrayList<String>();
	Statement st;
	try {
		st = (Statement) CcrmServer.con.conn.createStatement();
		ResultSet rs = st.executeQuery("SELECT mcID FROM ccrmg15.marketingcampaign");
			while(rs.next()){
				campaigns.add(rs.getString(1));
				//System.out.println(rs.getString(1));
			}
			b.setObj(campaigns);
			
			rs.close();
			st.close();
			return b;		
			
		} catch (SQLException e) {
				e.printStackTrace();
				}
	return null;
}
	
	}

		
	
